package com.css.zfzx.fwsjcj.modules.boundary.repository.impl;

import com.css.bpm.platform.utils.PlatformObjectUtils;
import com.css.zfzx.fwsjcj.modules.boundary.repository.BoundaryNativeRepository;
import com.css.zfzx.fwsjcj.modules.boundary.repository.entity.BoundaryEntity;
import com.css.zfzx.fwsjcj.modules.boundary.viewobjects.BoundaryQueryParams;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Repository;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author andy
 * @date 2021-01-26
 */
@Repository
public class BoundaryNativeRepositoryImpl implements BoundaryNativeRepository {
    @PersistenceContext
    private EntityManager em;

    private Map<String, List<BoundaryEntity>> parameter2coordinate = new HashMap<>();

    private static final Logger log = LoggerFactory.getLogger(BoundaryNativeRepositoryImpl.class);

    @Override
    public List<BoundaryEntity> listBoundaries(BoundaryQueryParams queryParams) {

        String type = queryParams.getType(); type = (type != null ? type : "1");//type  1: 行政区划 2: 普查区划
        String level = queryParams.getLevel(); level = (level != null ? level : "");
        String code = queryParams.getCode(); code = (code != null ? code : "");
        String name = queryParams.getCode(); name = (name != null ? name : "");
        String province = queryParams.getProvince(); province = (province != null ? province : "");
        String city = queryParams.getCity(); city = (city != null ? city : "");
        String area = queryParams.getArea(); area = (area != null ? area : "");

        if ("中华人民共和国".equals(province)) province = city;
        if ("北京市".equals(city) || "重庆市".equals(city)) city = "市辖区";

        // level 0:省 1:市 2: 区县
        if (!"".equals(province) && "".equals(city) && "".equals(area)) {
            level = "0";
            name = province;
        } else if (!"".equals(province) && !"".equals(city) && "".equals(area)) {
            level = "1";
            name = city;
        } else if (!"".equals(province) && !"".equals(city) && !"".equals(area)) {
            level = "2";
            name = area;
        } else {
            return new ArrayList<>();
        }

        String parameter = type + "-" + level + "-" + code + "-" + province + "-" + city + "-" + area;
        if (parameter2coordinate.containsKey(parameter)) return parameter2coordinate.get(parameter);

        StringBuilder sql = new StringBuilder("select * from district_boundary ");
        StringBuilder whereSql = new StringBuilder(" where 1=1 ");
        if(!PlatformObjectUtils.isEmpty(queryParams.getId())) {
            whereSql.append(" and id = :id");
        }
        if(!PlatformObjectUtils.isEmpty(type)) {
            whereSql.append(" and type = :type");
        }
        if(!PlatformObjectUtils.isEmpty(level)) {
            whereSql.append(" and level = :level");
        }
        if(!PlatformObjectUtils.isEmpty(code)) {
            whereSql.append(" and code = :code");
        }
        if(!PlatformObjectUtils.isEmpty(name)) {
            whereSql.append(" and name = :name");
        }

        if ("".equals(code)) {
            if("1".equals(level)) {
                whereSql.append(" and SUBSTRING(code, 1, 2)=(select SUBSTRING(code, 1, 2) from district_boundary where type='" + type + "' and level='0' and name='" + province + "')");
            } else if("2".equals(level)) {
                whereSql.append(" and SUBSTRING(code, 1, 4)=(select SUBSTRING(code, 1, 4) from district_boundary where type='" + type + "' and level='1' and name='" + city + "' and SUBSTRING(code, 1, 2)=(select SUBSTRING(code, 1, 2) from district_boundary where type='" + type + "' and level='0' and name='" + province + "'))");
            }
        }

        Query query = this.em.createNativeQuery(sql.append(whereSql).toString(), BoundaryEntity.class);
        if(!PlatformObjectUtils.isEmpty(queryParams.getId())) {
            query.setParameter("id", queryParams.getId());
        }
        if(!PlatformObjectUtils.isEmpty(type)) {
            query.setParameter("type", type);
        }
        if(!PlatformObjectUtils.isEmpty(level)) {
            query.setParameter("level", level);
        }
        if(!PlatformObjectUtils.isEmpty(code)) {
            query.setParameter("code", code);
        }
        if(!PlatformObjectUtils.isEmpty(name)) {
            query.setParameter("name",  name);
        }
        List<BoundaryEntity> list = query.getResultList();
        if (list.size() > 0) parameter2coordinate.put(parameter, list);
        return list;
    }


    @Override
    public List<BoundaryEntity> listAreasBoundaryByProvinceAndCity(String province, String city, String area, String type) {

        String level = "2";// level 0:省 1:市 2: 区县
        province = (province != null ? province : "");
        city = (city != null ? city : "");
        type = (type != null ? type : "1");//type  1: 行政区划 2: 普查区划

        if ("中华人民共和国".equals(province)) province = city;
        if ("北京市".equals(city) || "重庆市".equals(city)) city = "市辖区";

        if ("".equals(province) || "".equals(city)) return new ArrayList<>();

        String parameter = type + "-" + level + "-" + province + "-" + city + "-" + area;
        if (parameter2coordinate.containsKey(parameter)) return parameter2coordinate.get(parameter);

        StringBuilder sql = new StringBuilder("select * from district_boundary ");
        StringBuilder whereSql = new StringBuilder(" where 1=1 ");

        if(!PlatformObjectUtils.isEmpty(type)) {
            whereSql.append(" and type = :type");
        }
        if(!PlatformObjectUtils.isEmpty(level)) {
            whereSql.append(" and level = :level");
        }
        if(!PlatformObjectUtils.isEmpty(area)) {
            whereSql.append(" and name = :name");
        }

        whereSql.append(" and SUBSTRING(code, 1, 4)=(select SUBSTRING(code, 1, 4) from district_boundary where type='" + type + "' and level='1' and name='" + city + "' and SUBSTRING(code, 1, 2)=(select SUBSTRING(code, 1, 2) from district_boundary where type='" + type + "' and level='0' and name='" + province + "'))");

        Query query = this.em.createNativeQuery(sql.append(whereSql).toString(), BoundaryEntity.class);

        if(!PlatformObjectUtils.isEmpty(type)) {
            query.setParameter("type", type);
        }
        if(!PlatformObjectUtils.isEmpty(level)) {
            query.setParameter("level", level);
        }
        if(!PlatformObjectUtils.isEmpty(area)) {
            query.setParameter("name",  area);
        }

        List<BoundaryEntity> list = query.getResultList();
        if (list.size() > 0) parameter2coordinate.put(parameter, list);
        return list;
    }


    @Override
    public List<BoundaryEntity> listBoundariesByCoordinate(BoundaryQueryParams queryParams) {

        String type = queryParams.getType(); type = (type != null ? type : "1");//type  1: 行政区划 2: 普查区划
        String level = queryParams.getLevel(); level = (level != null ? level : "");
        String code = queryParams.getCode(); code = (code != null ? code : "");
        String name = queryParams.getCode(); name = (name != null ? name : "");
        String province = queryParams.getProvince(); province = (province != null ? province : "");
        String city = queryParams.getCity(); city = (city != null ? city : "");
        String area = queryParams.getArea(); area = (area != null ? area : "");

        if ("中华人民共和国".equals(province)) province = city;
        if ("北京市".equals(city) || "重庆市".equals(city)) city = "市辖区";

        // level 0:省 1:市 2: 区县
        if (!"".equals(province) && "".equals(city) && "".equals(area)) {
            level = "0";
            name = province;
        } else if (!"".equals(province) && !"".equals(city) && "".equals(area)) {
            level = "1";
            name = city;
        } else if (!"".equals(province) && !"".equals(city) && !"".equals(area)) {
            level = "2";
            name = area;
        }

        StringBuilder sql = new StringBuilder("select * from district_boundary ");
        StringBuilder whereSql = new StringBuilder(" where 1=1 ");
        if(!PlatformObjectUtils.isEmpty(queryParams.getId())) {
            whereSql.append(" and id = :id");
        }
        if(!PlatformObjectUtils.isEmpty(type)) {
            whereSql.append(" and type = :type");
        }
        if(!PlatformObjectUtils.isEmpty(level)) {
            whereSql.append(" and level = :level");
        }
        if(!PlatformObjectUtils.isEmpty(code)) {
            whereSql.append(" and code = :code");
        }
        if(!PlatformObjectUtils.isEmpty(name)) {
            whereSql.append(" and name = :name");
        }

        if ("".equals(code)) {
            if("1".equals(level)) {
                whereSql.append(" and SUBSTRING(code, 1, 2)=(select SUBSTRING(code, 1, 2) from district_boundary where type='" + type + "' and level='0' and name='" + province + "')");
            } else if("2".equals(level)) {
                whereSql.append(" and SUBSTRING(code, 1, 4)=(select SUBSTRING(code, 1, 4) from district_boundary where type='" + type + "' and level='1' and name='" + city + "' and SUBSTRING(code, 1, 2)=(select SUBSTRING(code, 1, 2) from district_boundary where type='" + type + "' and level='0' and name='" + province + "'))");
            }
        }

        String coordinate = queryParams.getCoordinate();
        if (!PlatformObjectUtils.isEmpty(coordinate)) {
            String spaceParameter = "", lon = "", lat = "";
            String[] coordinates = coordinate.split(";"), XY = null;
            for (int i = 0; i < coordinates.length; i++) {
                XY = coordinates[i].split(",");
                lon = XY[1]; lat = XY[0];
                spaceParameter += lon + " " + lat + ", ";
            }
            XY = coordinates[0].split(",");
            lon = XY[1]; lat = XY[0];
            spaceParameter += lon + " " + lat;
            whereSql.append(" and MBRIntersects(ST_GeomFromText('Polygon((" + spaceParameter + "))'),ST_GeomFromText(coordinate)) ");
        }

        Query query = this.em.createNativeQuery(sql.append(whereSql).toString(), BoundaryEntity.class);
        if(!PlatformObjectUtils.isEmpty(queryParams.getId())) {
            query.setParameter("id", queryParams.getId());
        }
        if(!PlatformObjectUtils.isEmpty(type)) {
            query.setParameter("type", type);
        }
        if(!PlatformObjectUtils.isEmpty(level)) {
            query.setParameter("level", level);
        }
        if(!PlatformObjectUtils.isEmpty(code)) {
            query.setParameter("code", code);
        }
        if(!PlatformObjectUtils.isEmpty(name)) {
            query.setParameter("name",  name);
        }
        List<BoundaryEntity> list = query.getResultList();
        return list;
    }

}
